Create a Project Database

For this example, the script will be executed using SQL Server Management Studio in conjunction with SQL Server 2014, which is installed via the OpenUtilities Substation installation package. The SQL Server Management Studio will need to be downloaded from the Microsoft downloads site and installed separately.

This procedure details how to create a project database by modifying and executing the SqlServerProject.sql script using SQL Server Management Studio, sqlcmd, or the osql utility. For an Oracle database the script is OracleProject.sql . When the database is to be created on a machine other than the workstation, the script can be can be copied to the server machine from the workstation that has OpenUtilities Substation installed, then executed by the database administrator.

In CONNECT Edition, the script can be found in the OpenUtilities Substation program folder:

C:\Program Files\Bentley\OpenUtilities Substation CONNECT Edition\Substation\Electrical\SqlScripts\

Note: If the file cannot be found, perform a search. Make a backup copy of the script before modifying it.

Navigate to the script file in Windows Explorer and double click it. On machines where SQL Server Management Studio is installed, double clicking on a .sql file will open it in SQL Server Management Studio.
When the Connect To Database window appears, enter the name of the server machine and instance where the database to be updated resides in the format MACHINENAME\INSTANCENAME. Consulting the Project Database tab of the software's Setup dialog may help determine what should be entered here. Select the appropriate Authentication method, then click Connect.
Note: Refer to the script excerpt shown in the Script Excerpt section below. The excerpt shows how the script would look for creating a database named "Substation1" with paths typical of a SQL Server 2014 Express installation with a BENTLEYECAD instance.
Remove the following line which is related to preventing performance degradation: ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ON
Alternatively, change ON to OFF for that line. The line will already be set to OFF in recent versions.
Replace the instances of "$ShortProductName$" with the name of the database to be created.
CAUTION: DO NOT USE FIND AND REPLACE to replace all instances of this text throughout the script or you will introduce an error by replacing too many instances of this text. Only replace the ones indicated.
The "$ShortProductName$" value in the last line of the excerpt must be changed to 'Substation' regardless of the name of database, and it is case-sensitive.
Modify the paths for the database and transaction log files to reflect the true path to be used. A typical path for SQL Server 2014 is:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\DATA

Note: Executing the script will not create any folders in the specified path. All folders in the path must exist at the time the script is executed.
After making the necessary changes, click the Execute button. After successfully executing the script in SQL Server Management Studio, the Messages pane should display many lines of "(1 row(s) affected)" with no errors.

Script Excerpt

use master

GO

CREATE DATABASE Substation1

ON (NAME = N'Substation1',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\Substation1.mdf',SIZE = 200,FILEGROWTH = 40%)

LOG ON (NAME = N'promise1_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\promise1_log.ldf',SIZE = 80,MAXSIZE = 200,FILEGROWTH = 20%)

ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ON

GO

ALTER DATABASE Substation1 SET RECOVERY SIMPLE

GO

use Substation1

GO

ALTER DATABASE promise1 SET AUTO_CLOSE OFF

GO

SET QUOTED_IDENTIFIER ON

GO

--********************************************************************--

if exists (select * from master..sysdatabases WHERE name=N'promise1')

begin

CREATE TABLE Project (

id int IDENTITY (1, 1) NOT NULL,

name nvarchar(255) NOT NULL,

anum nvarchar(255) NULL,

templatename nvarchar(255) NULL,

username nvarchar(50) NULL,

date_create datetime NOT NULL,

version nvarchar(255) NULL,

prjpath nvarchar(255) NULL,

guid nvarchar(50) NULL,

ModifyTime datetime NULL,

ConnectionBuildTime datetime NULL,

isLock int NULL DEFAULT 0,

refid int NULL,

CONSTRAINT PK_Project PRIMARY KEY (id)

);

EXEC sp_addextendedproperty 'Version', '8.0.69', 'user', dbo, 'table', project;

EXEC sp_addextendedproperty 'ShortProductName', ' Substation ', 'user', dbo, 'table', project;